const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *出库反审接口
 */


// let startTime = '2019-05-17 00:00:00';
// let startTime = '';
//  获得订单信息

const getOutBackVerifyOrder = async (ctx, next) => {
    const body = ctx.request.body;
    const searchVal = body.searchVal;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,Z.SH,Z.RQ,Z.ID,Z.DWBM,Z.BMBM,Z.BMMC,Z.DWMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.ID_DHD,B.DH FROM XSD_ZB Z LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  WHERE  Z.BMBM='${searchVal.BMBM}'  `;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL FROM XSD_ZB Z LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  WHERE  Z.BMBM='${searchVal.BMBM}' `
    if (searchVal.DWMC) {
        startSql += ` AND (B.MC LIKE '%${searchVal.DWMC}%' OR B.DH LIKE '%${searchVal.DWMC}%')`;
        totalSql += ` AND (B.MC LIKE '%${searchVal.DWMC}%' OR B.DH LIKE '%${searchVal.DWMC}%')`;
    }
    if (searchVal.ID) {
        startSql += ` AND Z.ID = '${searchVal.ID}' `;
        totalSql += ` AND Z.ID = '${searchVal.ID}' `;
    }
    if (searchVal.ID_DHD) {
        startSql += ` AND Z.ID_DHD = '${searchVal.ID_DHD}' `;
        totalSql += ` AND Z.ID_DHD = '${searchVal.ID_DHD}' `;
    }

    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    startSql += endSql;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}


//  获得订单对应的商品信息
const getOutBackVerifyShop = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT M.YPBM,M.YPTM,M.YPMC,M.YPGG,M.JXMC,M.SCCJ,M.CDMC,M.PZWH,M.SCPH,M.SCRQ,M.YXQX,M.JXQX,M.ZPCX,M.TSFL,M.XGSL,M.JFGZ,
    M.YXQZ,M.JLDW,M.MJSL,M.XS,M.SL,M.JS,M.SS,M.YJ,M.YJ_HS,M.ZK,M.DJ,M.DJ_HS,M.DJ_LSJ,M.DJ_ZDJ,M.JE,M.RT,M.SE,M.HJ,M.CBJJ,M.XSCB,M.XSML,M.CKBM,
    M.CKMC,M.ZP,M.DZ,M.JF,M.CB,M.FHRQ,M.BZQK,M.ZLZK,M.FHJL,M.HWBH,Y.YPLX,Y.CCTJ,Y.JG_FLAG,Y.YXFL,Y.SSXK,Y.JP,Z.BMBM,T1.DDSL,K.KCSL FROM XSD_MX M  
    LEFT JOIN XSD_ZB Z ON M.ID = Z.ID LEFT JOIN KC_KCDTB K ON K.BMBM = Z.BMBM AND K.CKBM = M.CKBM AND K.YPBM = M.YPBM AND K.SCPH = M.SCPH 
    LEFT JOIN (SELECT SUM(SL) AS DDSL ,YPBM,ID FROM XDD_MX GROUP BY YPBM,ID) T1 ON T1.ID = Z.ID_DHD AND T1.YPBM = M.YPBM LEFT JOIN BM_YP Y ON 
    M.YPBM =  Y.BM   WHERE M.ID = '${body.orderId}' `;
    let result = await sql.query(startSql);

    ctx.body = {
        data: result.recordset
    };
}
// 把订单的审核状态改成F，变还原库存

const cancelOutBackVerify = async (ctx, next) => {
    const body = ctx.request.body;
    let updateZbResult = await sql.query(`UPDATE XSD_ZB SET SH = 'F' WHERE ID = '${body.orderId}'`);
    let affectedCount = updateZbResult.rowsAffected[0]
    if (affectedCount > 0) {
        body.shopList.forEach(async (item, ind) => {
            //  还原库存
            let kcslResult = await sql.query(`SELECT KCSL,KYSL,CBJJ FROM KC_KCDTB WHERE BMBM ='${item.BMBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}'`);
            if (kcslResult.rowsAffected[0] > 0) {
                let kcsl = Number(kcslResult.recordset[0].KCSL) + Number(item.SL);
                let kysl = Number(kcslResult.recordset[0].KYSL) + Number(item.SL);
                let cbjj = Number(kcslResult.recordset[0].cbjj) ;
                let kcje = (cbjj*kcsl).toFixed(2);
                await sql.query(`UPDATE KC_KCDTB SET KCSL = ${kcsl},KYSL = ${kysl},KCJE = ${kcje} WHERE BMBM ='${item.BMBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}'`);
            } else {
                let insertKcSql = `INSERT INTO KC_KCDTB(BMBM,CKBM,CKMC,YPBM,SCPH,RKRQ,SCRQ,YXQX,YXQZ,KCSL,KYSL,CBJJ,KCJE,DWBM,DWMC,HWBH,YHRQ) VALUES 
                ('${item.BMBM}','${item.CKBM}','${item.CKMC}','${item.YPBM}','${item.SCPH}','${baseFn.getNowTimeId().fullDate}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}',
                ${item.SL},${item.SL},${item.CBJJ},${(item.CBJJ * item.SL).toFixed(2)},'','','${item.HWBH}','')`;
                await sql.query(insertKcSql);
            }
        });
        ctx.body = {
            success: true
        };
    } else {
        ctx.body = {
            success: false
        };
    }



}

//  保存订单信息
const keepOutBackVerifyOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let shopInfor = body.shopList;
    let orderId = body.orderId;
    // 删除订单明细
    await sql.query(`DELETE FROM XSD_MX WHERE ID = '${orderId}'`);
    /**
   *    写入销售订单明细表
   */
    let insertMxSql = `INSERT INTO XSD_MX (
        ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,JXQX,YXQZ,JLDW,MJSL,XS,SL,
        JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,DJ_LSJ,DJ_ZDJ,JE,RT,SE,HJ,ZKJE,CBJJ,XSCB,XSML,CKBM,CKMC,HWBH,
        JFGZ,SSYP,ZPCX,XGSL,YXFL,TSFL,KB,ZP,DZ,JF,CB,JG,UID,FHRQ,BZQK,ZLZK,FHJL,FHR2,YPLX,KCSL,SN,Notes,DJ_ZGJ) 
        `;
    // let zje = 0, zss = 0, zjs = 0, zsl = 0, zhj = 0, zcb = 0, zml = 0;

    shopInfor.forEach(async (item, ind) => {

        let dj = Number(item.DJ ? item.DJ : 0);
        let sl = Number(item.CKSL ? item.CKSL : 0);
        let zk = Number(item.ZK ? item.ZK : 0);
        let cbjj = Number(item.CBJJ ? item.CBJJ : 0);
        let mjsl = Number(item.MJSL ? item.MJSL : 0);
        let js = mjsl === 0 ? 0 : Math.floor(sl / mjsl);
        let ss = sl - (mjsl * js);
        let je = (dj * sl).toFixed(2);
        let hj = ((dj * zk / 100) * sl).toFixed(2);
        let zkje = je - hj;
        let xscb = (cbjj * sl).toFixed(2);
        let xsml = (hj - xscb).toFixed(2);
        // zsl += sl;
        // zje += Number(je);
        // zss += Number(ss);
        // zjs += Number(js);
        // zhj += Number(hj);
        // zcb += Number(xscb);
        // zml += Number(xsml);
        let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);

        insertMxSql += `SELECT '${orderId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM}','${item.YPMC}','${item.YPGG}'
            ,'${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},${item.JXQX},'${item.YXQZ}','${item.JLDW}'
            ,${mjsl},${sl},${sl},${js},${ss},${item.DJ},${item.DJ},${item.ZK},${item.DJ},${item.DJ},${item.DJ_LSJ},${item.DJ_ZDJ},${je},0,0,${hj},${zkje}
            ,${item.CBJJ},${xscb},${xsml},'${item.CKBM}','${item.CKMC}','${item.HWBH}','${item.JFGZ}','','${item.ZPCX}',${item.XGSL},'${item.YXFL}',
            '${item.TSFL}','','F','T','T','A','F','NULL','','合格','合格','合格','','${item.YPLX}',${item.KCSL},${baseFn.getNowTimeId().msDate + xhSerialNum},'',NULL `;


        if (ind !== (shopInfor.length - 1)) {
            insertMxSql += ` UNION `
        }

    });
    // // 更新总表和结算表
    // let updateJsSql = `UPDATE XSD_JS SET JSJE = ${zje},SK=${zje} WHERE ID = '${orderId}' `;
    // let updateZbSql = ` UPDATE XSD_ZB SET ZJE = ${zje},ZCB = ${zcb},ZML = ${zml},ZSS = ${zss},ZJS = ${zjs},ZSL = ${zsl},SH='T',SHR = '${body.SHR}' WHERE ID = '${orderId}'`;
    let updateZbSql = ` UPDATE XSD_ZB SET SH='T',SHR = '${body.SHR}' WHERE ID = '${orderId}'`;
    let zbResult = await sql.query(updateZbSql);

    let mxResult = await sql.query(insertMxSql);
    // let jsResult = await sql.query(updateJsSql);
    // if (zbResult.rowsAffected[0] > 0 && jsResult.rowsAffected[0] > 0 && mxResult.rowsAffected[0] > 0) {
    if (zbResult.rowsAffected[0] > 0 && mxResult.rowsAffected[0] > 0) {
        /**
    * 如果插入销售明细表后减库存
    *  */

        shopInfor.forEach(async (item, ind) => {
            /**
        *   减库存
        */
            let kcslResult = await sql.query(`SELECT KCSL,KYSL,CBJJ FROM KC_KCDTB WHERE BMBM ='${body.BMBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}'`);
            let kcsl = kcslResult.recordset[0].KCSL;
            let kysl = kcslResult.recordset[0].KYSL;
            let cbjj = Number(kcslResult.recordset[0].CBJJ) ;
            let kcje = (cbjj*(kcsl - item.CKSL)).toFixed(2);
            await sql.query(`UPDATE KC_KCDTB SET KCSL = ${kcsl - item.CKSL},KYSL = ${kysl - item.CKSL},KCJE = ${kcje} WHERE BMBM ='${body.BMBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}'`);
        });

        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }


}

module.exports = {
    'POST /getOutBackVerifyOrder': getOutBackVerifyOrder,
    'POST /getOutBackVerifyShop': getOutBackVerifyShop,
    'POST /cancelOutBackVerify': cancelOutBackVerify,
    'POST /keepOutBackVerifyOrder': keepOutBackVerifyOrder

}
